package cn.bone.excel;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public class ExcelMain2 {

    public static void main(String... args){
        execute2();
    }

    // 2021湖北高职高专招生计划·普通类.xlsx
    public static void execute1(){
        DataParam param1 = new DataParam();
        param1.setFileName("e:/tmp/excel/2021湖北高职高专招生计划·普通类.xlsx");
        param1.setSheetAt(0);
        param1.setName1At(1);
        param1.setName2At(2);
        param1.setNameCodeAt(0);
        param1.setNameValueAt(5);
        param1.setLastRow(8965);
        List<NameData> list1 = ExcelUtil.readSheet(param1);

        DataParam param2 = new DataParam();
        param2.setFileName("e:/tmp/excel/2021湖北高职高专招生计划·普通类.xlsx");
        param2.setSheetAt(1);
        param2.setName1At(1);
        param2.setName2At(2);
        param2.setNameCodeAt(0);
        param2.setNameValueAt(5);
        param2.setLastRow(4687);
        List<NameData> list2 = ExcelUtil.readSheet(param2);

        DataParam param3 = new DataParam();
        param3.setFileName("e:/tmp/excel/2021湖北高职高专招生计划·普通类.xlsx");
        param3.setSheetAt(2);
        param3.setName1At(1);
        param3.setName2At(2);
        param3.setNameCodeAt(0);
        param3.setNameValueAt(5);
        param3.setLastRow(4220);
        List<NameData> list3 = ExcelUtil.readSheet(param3);

        DataParam param4 = new DataParam();
        param4.setFileName("e:/tmp/excel/2021湖北高职高专招生计划·普通类.xlsx");
        param4.setSheetAt(3);
        param4.setName1At(1);
        param4.setName2At(2);
        param4.setNameCodeAt(0);
        param4.setNameValueAt(5);
        param4.setLastRow(60);
        List<NameData> list4 = ExcelUtil.readSheet(param4);

        DataParam param5 = new DataParam();
        param5.setFileName("e:/tmp/excel/2021湖北高职高专招生计划·普通类.xlsx");
        param5.setSheetAt(4);
        param5.setName1At(1);
        param5.setName2At(2);
        param5.setNameCodeAt(0);
        param5.setNameValueAt(5);
        param5.setLastRow(187);
        List<NameData> list5 = ExcelUtil.readSheet(param5);

        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet0 = workbook.createSheet("总表");
        ExcelUtil.createSheet(sheet0, list1);
        XSSFSheet sheet1 = workbook.createSheet("物理");
        ExcelUtil.createSheet(sheet1, list2);
        XSSFSheet sheet2 = workbook.createSheet("历史");
        ExcelUtil.createSheet(sheet2, list3);
        XSSFSheet sheet3 = workbook.createSheet("联合培养试点");
        ExcelUtil.createSheet(sheet3, list4);
        XSSFSheet sheet4 = workbook.createSheet("提前批");
        ExcelUtil.createSheet(sheet4, list5);

        String outName = "e:/tmp/excel_arrange/2021湖北高职高专招生计划·普通类_1.xlsx";
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(outName);
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(workbook!=null){
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(outputStream!=null){
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }


    // 2021湖北高职高专招生计划·普通类.xlsx
    public static void execute2(){
        DataParam param1 = new DataParam();
        param1.setFileName("e:/tmp/excel/2021年湖北历史+X本科院校招生计划.xlsx");
        param1.setSheetAt(0);
        param1.setName1At(1);
        param1.setName2At(2);
        param1.setNameCodeAt(3);
        param1.setNameValueAt(7);
        param1.setLastRow(5445);
        List<NameData> list1 = ExcelUtil.readSheet(param1);

        DataParam param2 = new DataParam();
        param2.setFileName("e:/tmp/excel/2021年湖北历史+X本科院校招生计划.xlsx");
        param2.setSheetAt(1);
        param2.setName1At(2);
        param2.setName2At(3);
        param2.setNameCodeAt(0);
        param2.setNameValueAt(7);
        param2.setLastRow(4451);
        List<NameData> list2 = ExcelUtil.readSheet(param2);

        DataParam param3 = new DataParam();
        param3.setFileName("e:/tmp/excel/2021年湖北历史+X本科院校招生计划.xlsx");
        param3.setSheetAt(2);
        param3.setName1At(2);
        param3.setName2At(3);
        param3.setNameCodeAt(0);
        param3.setNameValueAt(7);
        param3.setLastRow(179);
        List<NameData> list3 = ExcelUtil.readSheet(param3);

        DataParam param4 = new DataParam();
        param4.setFileName("e:/tmp/excel/2021年湖北历史+X本科院校招生计划.xlsx");
        param4.setSheetAt(3);
        param4.setName1At(2);
        param4.setName2At(3);
        param4.setNameCodeAt(0);
        param4.setNameValueAt(7);
        param4.setLastRow(88);
        List<NameData> list4 = ExcelUtil.readSheet(param4);


        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet0 = workbook.createSheet("总表");
        ExcelUtil.createSheet(sheet0, list1);
        XSSFSheet sheet1 = workbook.createSheet("普通类");
        ExcelUtil.createSheet(sheet1, list2);
        XSSFSheet sheet2 = workbook.createSheet("国家专项");
        ExcelUtil.createSheet(sheet2, list3);
        XSSFSheet sheet3 = workbook.createSheet("地方专项");
        ExcelUtil.createSheet(sheet3, list4);


        String outName = "e:/tmp/excel_arrange/2021年湖北历史+X本科院校招生计划_1.xlsx";
        FileOutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(outName);
            workbook.write(outputStream);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(workbook!=null){
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(outputStream!=null){
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
